package fun.chutianshu.eescore_02_springbootMVC.dao;

import fun.chutianshu.eescore_02_springbootMVC.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class StudentDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public StudentDao(){}
    //增
    public int create(Student stu) {
        return jdbcTemplate.update("insert into student(class_name, college,grade_num,phone_num,profession,stu_code,stu_gender,stu_id,stu_name,birthday,photo,weixin_num,department) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                stu.getClass_name(),
                stu.getCollege(),
                stu.getGradeNum(),
                stu.getPhone_num(),
                stu.getProfession(),
                stu.getStu_code(),
                stu.getStu_gender(),
                stu.getStu_id(),
                stu.getStu_name(),
                stu.getBirthday(),
                stu.getPhoto(),
                stu.getWeixin_num(),
                stu.getDepartment());
    }

    // 删
    public int deleteByName(String stu_code) {
        return jdbcTemplate.update("delete from student where stu_code = ?", stu_code);
    }

    public int deleteAllStudents() {
        return jdbcTemplate.update("delete from student");
    }

    //改


    //查
    public List<Student> getAllStudents() {
        List<Student> Students = jdbcTemplate.query("select * from student", (resultSet, i) -> {
            Student Student = new Student();
            Student.setStu_name(resultSet.getString("stu_name"));
            Student.setClass_name(resultSet.getString("class_name"));
            Student.setBirthday(resultSet.getDate("birthday").toLocalDate());
            Student.setCollege(resultSet.getString("college"));
            Student.setDepartment(resultSet.getString("department"));
            Student.setGradeNum(resultSet.getString("grade_num"));
            Student.setPhone_num(resultSet.getString("phone_num"));
            Student.setPhoto(resultSet.getString("photo"));
            Student.setProfession(resultSet.getString("profession"));
            Student.setStu_code(resultSet.getInt("stu_code"));
            Student.setStu_id(resultSet.getString("stu_id"));
            Student.setStu_gender(resultSet.getInt("stu_gender"));
            Student.setWeixin_num(resultSet.getString("weixin_num"));
            return Student;
        });
        return Students;
    }
    public List<Student> getByName(String name) {
        List<Student> Students = jdbcTemplate.query("select NAME, AGE from Student where NAME = ?", (resultSet, i) -> {
            Student Student = new Student();
            Student.setStu_name(resultSet.getString("NAME"));

            return Student;
        }, name);
        return Students;
    }



    public int getAllStudentsCount() {
        return jdbcTemplate.queryForObject("select count(1) from Student", Integer.class);
    }


    
}
